package org.execute.tool.使用.提取Excel中两列对比;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelColumnExtractor {

//    public static void main(String[] args) {
//        String excelFilePath = "E:\\工作记录\\2025_1\\fb 导出数据库2025年1月11日192837.xlsx";
//        String sheetName = "fb 导出数据库";// 假设你要提取的表单名为Sheet1
////        int columnIndex = 0; // 假设你要提取第一列（A列）
////
////        List<String> columnData = extractColumnFromExcel(excelFilePath, sheetName, columnIndex);
////        for (String data : columnData) {
////            System.out.println(data);
////        }
//        int[] columnIndices = {0, 1, 2}; // 假设你要提取第一、第二和第三列
//        Map<Integer, List<String>> columnDataMap = extractColumnFromExcel(excelFilePath, sheetName, columnIndices);
//        for (Map.Entry<Integer, List<String>> entry : columnDataMap.entrySet()) {
//            for (Integer i = 0; i < entry.getValue().size(); i++) {
//                System.out.println("Column " + entry.getKey() + ": " + entry.getValue().get(i));
//            }
//
//        }
//    }

    public static List<String> extractColumnFromExcel(String excelFilePath, String sheetName, int columnIndex) {
        List<String> columnData = new ArrayList<>();
        FileInputStream inputStream = null;
        Workbook workbook = null;

        try {
            inputStream = new FileInputStream(excelFilePath);
            workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheet(sheetName);

            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Cell cell = row.getCell(columnIndex);
                if (cell != null) {
                    columnData.add(cell.toString());
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return columnData;
    }

    public static Map<Integer, List<String>> extractColumnFromExcel(String excelFilePath, String sheetName, int[] columnIndex) {
        Map<Integer, List<String>> columnData = new HashMap<>();
        FileInputStream inputStream = null;
        Workbook workbook = null;

        try {
            inputStream = new FileInputStream(excelFilePath);
            workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheet(sheetName);

            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                for (int index : columnIndex) {
                    Cell cell = row.getCell(index);
                    List<String> strings = columnData.get(index);
                    if (strings == null) {
                        strings = new ArrayList<>();
                        columnData.put(index, strings);
                    }
                    if (cell != null) {



                        CellType cellType = cell.getCellType();
                        // 根据单元格的类型进行处理
                        switch (cellType) {
                            case STRING:
                                // 如果单元格是字符串类型，直接添加到列表中
                                strings.add(cell.getStringCellValue());
                                break;
                            case NUMERIC:
                                // 如果单元格是数字类型，检查是否是日期类型
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    // 如果是日期类型，使用 SimpleDateFormat 进行格式化
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                    String formattedValue = sdf.format(cell.getDateCellValue());
                                    strings.add(formattedValue);
                                } else {
                                    // 如果不是日期类型，使用 DecimalFormat 进行格式化
                                    DecimalFormat df = new DecimalFormat("#");
                                    String formattedValue = df.format(cell.getNumericCellValue());
                                    strings.add(formattedValue);
                                }
                                break;
                            case BOOLEAN:
                                // 如果单元格是布尔类型，添加布尔值的字符串表示
                                strings.add(String.valueOf(cell.getBooleanCellValue()));
                                break;
                            case FORMULA:
                                // 如果单元格是公式类型，添加公式的字符串表示
                                strings.add(cell.getCellFormula());
                                break;
                            case BLANK:
                                // 如果单元格是空白类型，添加一个空字符串
                                strings.add("");
                                break;
                            default:
                                // 如果单元格是其他类型，添加一个空字符串
                                strings.add("");
                                break;
                        }

                    }else {
                        strings.add("");
                    }
                }

            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return columnData;
    }

}
